﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
using System.IO;
using System.Transactions;
 


namespace FederationMembershipDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Backup the Database
            //TODO: Backup DB not working...Try moving DB to local dir?
            //Utilities.BackupDatabase(@"Federation_Database.mdf", @"C:\Temp\");

            //Purchasing controls should be disabled until a member ID has been looked up
            purchEnableControls(false);
            ddlToAdminister.SelectedIndex = 0;

            //We need to prepare the comboboxes on the class forms and disable the edit controls until an ID has been looked up
            classPrepareForms();
            EnableClassAdminControls(false);
        }


        #region Member/Student

        private void btnAddStudent_Click(object sender, EventArgs e)
        {
            int MemberID;
            bool validNumber = int.TryParse(txtID.Text, out MemberID);

            if (!validNumber)
            {
                MessageBox.Show("Member ID must be a number");
                return;
            }

            //lookup memberID in the database
            int count = Utilities.lookupMemberID(MemberID);            

            if (count != 0)
            {
                MessageBox.Show("Member ID: " + txtID.Text + " already exists");
            }
            else
            {
                Member newMember = new Member();
                EmergencyContact newEmergencyContact = new EmergencyContact();
                MedicalHistory newMedicalHistory = new MedicalHistory();

                newMedicalHistory.AddMedicalHistory(chkAilments.Checked, txtMedNotes.Text, chkAuthorisation.Checked);
                newEmergencyContact.AddEmergencyContact(txtEmergName.Text, txtEmergRelationship.Text, txtEmergPhone.Text, txtEmergMobile.Text);
                newMember.AddMember(Convert.ToInt32(txtID.Text), txtGivenName.Text, txtSurname.Text, cmbGender.SelectedText, dtpDOB.Value.Date, txtAddPicture.Text, txtHmphone.Text, txtWkphone.Text, txtMobile.Text, txtEmail.Text, txtSkype.Text, txtAddress.Text, txtMartialArtsHistory.Text, txtComments.Text, newEmergencyContact, newMedicalHistory);

                MessageBox.Show("Member successfully added");
            }
        }

        private void btnDisLookupID_Click(object sender, EventArgs e)
        {
            int MemberID;
            bool validNumber = int.TryParse(txtDisMemberID.Text, out MemberID);

            if (!validNumber)
            {
                MessageBox.Show("Member ID must be a number");
                return;
            }

            //lookup memberID in the database
            int count = Utilities.lookupMemberID(MemberID);

            if (count == 0)
            {
                MessageBox.Show("Member ID: " + txtDisMemberID.Text + " doesn't exist");
                return;
            }

            string[] MemberDetails = new string[13];
            string[] EmergencyContactDetails = new string[4];
            string[] MedicalHistoryDetails = new string[3];

            Member displayMember = new Member();
            MedicalHistory displayMedicalHistory = new MedicalHistory();
            EmergencyContact displayEmergencyContact = new EmergencyContact();

            MemberDetails = displayMember.ViewMember(MemberID);
            EmergencyContactDetails = displayEmergencyContact.ViewEmergencyContact(MemberID);
            MedicalHistoryDetails = displayMedicalHistory.ViewMedicalHistory(MemberID);

            txtDisGivenName.Text = MemberDetails[0];
            txtDisSurname.Text = MemberDetails[1];
            cmbDisGender.Text = MemberDetails[2];
            dtpDisDOB.Text = MemberDetails[3];
            txtDisPicture.Text = MemberDetails[4];
            txtDisHmphone.Text = MemberDetails[5];
            txtDisWkphone.Text = MemberDetails[6];
            txtDisMobile.Text = MemberDetails[7];
            txtDisEmail.Text = MemberDetails[8];
            txtDisSkype.Text = MemberDetails[9];
            txtDisAddress.Text = MemberDetails[10];
            txtDisMartialArtsHistory.Text = MemberDetails[11];
            txtDisComments.Text = MemberDetails[12];

            txtDisEmergName.Text = EmergencyContactDetails[0];
            txtDisEmergRelationship.Text = EmergencyContactDetails[1];
            txtDisEmergPhone.Text = EmergencyContactDetails[2];
            txtDisEmergMobile.Text = EmergencyContactDetails[3];

            if (MedicalHistoryDetails[0] == "T")
                chkDisAilments.Checked = true;
            else
                chkDisAilments.Checked = false;

            if (MedicalHistoryDetails[1] == "T")
                chkDisAuthorisation.Checked = true;
            else
                chkDisAuthorisation.Checked = false;

            txtDisMedNotes.Text = MedicalHistoryDetails[2];

            try
            {
                //Load image from filename
                if (txtDisPicture.Text == "")
                    picDisplayPicture = null;
                else
                    picDisplayPicture.Image = Image.FromFile(txtDisPicture.Text, true);
            }

            catch (System.IO.FileNotFoundException)
            {
                MessageBox.Show("Image file not found");
            }

        }

        private void btnResetForm_Click(object sender, EventArgs e)
        {
            txtID.Text = "";
            txtGivenName.Text = "";
            txtSurname.Text = "";
            dtpDOB.Text = "";
            cmbGender.Text = "Female";
            txtHmphone.Text = "";
            txtWkphone.Text = "";
            txtMobile.Text = "";
            txtEmail.Text = "";
            txtSkype.Text = "";
            txtAddress.Text = "";
            txtMartialArtsHistory.Text = "";
            txtComments.Text = "";
            txtAddPicture.Text = "";
            picAddPicture.Image = null;

            chkAilments.Checked = false;
            txtMedNotes.Text = "";
            chkAuthorisation.Checked = false;

            txtEmergName.Text = "";
            txtEmergRelationship.Text = "";
            txtEmergPhone.Text = "";
            txtEmergMobile.Text = "";
        }

        private void btnEditStudent_Click(object sender, EventArgs e)
        {
            Member newMember = new Member();
            EmergencyContact newEmergencyContact = new EmergencyContact();
            MedicalHistory newMedicalHistory = new MedicalHistory();

            newMedicalHistory.UpdateMedicalHistory(Convert.ToInt32(txtEditMemberID.Text), chkEditAilments.Checked, txtEditMedNotes.Text, chkEditAuthorisation.Checked);
            newEmergencyContact.UpdateEmergencyContact(Convert.ToInt32(txtEditMemberID.Text), txtEditEmergName.Text, txtEditEmergRelationship.Text, txtEditEmergPhone.Text, txtEditEmergMobile.Text);
            newMember.EditMember(Convert.ToInt32(txtEditMemberID.Text), txtEditGivenName.Text, txtEditSurname.Text, cmbEditGender.SelectedText, dtpEditDOB.Value.Date, txtEditPicture.Text, txtEditHmphone.Text, txtEditWkphone.Text, txtEditMobile.Text, txtEditEmail.Text, txtEditSkype.Text, txtEditAddress.Text, txtEditMartialArtsHistory.Text, txtEditComments.Text);
            MessageBox.Show("Member successfully updated");
        
        }

        private void btnEditLookupID_Click(object sender, EventArgs e)
        {
            int MemberID;
            bool validNumber = int.TryParse(txtEditMemberID.Text, out MemberID);

            if (!validNumber)
            {
                MessageBox.Show("Member ID must be a number", "Invalid Member ID", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            
            //lookup memberID in the database
            int count = Utilities.lookupMemberID(MemberID);

            if (count == 0)
            {
                MessageBox.Show("Member ID: " + txtDisMemberID.Text + " doesn't exist", "Invalid Member ID", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            string[] MemberDetails = new string[13];
            string[] EmergencyContactDetails = new string[4];
            string[] MedicalHistoryDetails = new string[3];

            Member displayMember = new Member();
            MedicalHistory displayMedicalHistory = new MedicalHistory();
            EmergencyContact displayEmergencyContact = new EmergencyContact();

            MemberDetails = displayMember.ViewMember(MemberID);
            EmergencyContactDetails = displayEmergencyContact.ViewEmergencyContact(MemberID);
            MedicalHistoryDetails = displayMedicalHistory.ViewMedicalHistory(MemberID);

            txtEditGivenName.Text = MemberDetails[0];
            txtEditSurname.Text = MemberDetails[1];
            cmbEditGender.Text = MemberDetails[2];
            dtpEditDOB.Text = MemberDetails[3];
            txtEditPicture.Text = MemberDetails[4];
            txtEditHmphone.Text = MemberDetails[5];
            txtEditWkphone.Text = MemberDetails[6];
            txtEditMobile.Text = MemberDetails[7];
            txtEditEmail.Text = MemberDetails[8];
            txtEditSkype.Text = MemberDetails[9];
            txtEditAddress.Text = MemberDetails[10];
            txtEditMartialArtsHistory.Text = MemberDetails[11];
            txtEditComments.Text = MemberDetails[12];

            txtEditEmergName.Text = EmergencyContactDetails[0];
            txtEditEmergRelationship.Text = EmergencyContactDetails[1];
            txtEditEmergPhone.Text = EmergencyContactDetails[2];
            txtEditEmergMobile.Text = EmergencyContactDetails[3];

            if (MedicalHistoryDetails[0] == "T")
                chkEditAilments.Checked = true;
            else
                chkEditAilments.Checked = false;

            if (MedicalHistoryDetails[1] == "T")
                chkEditAuthorisation.Checked = true;
            else
                chkEditAuthorisation.Checked = false;

            txtEditMedNotes.Text = MedicalHistoryDetails[2];

            btnEditStudent.Visible = true;
            btnResetEditStudent.Visible = true;
            btnEditLookupID.Visible = false;
            txtEditMemberID.ReadOnly = true;

            try
            {
                //Load image from filename
                if (txtDisPicture.Text == "")
                    picEditPicture.Image = null;
                else
                    picEditPicture.Image = Image.FromFile(txtDisPicture.Text, true);
            }

            catch (System.IO.FileNotFoundException)
            {
                MessageBox.Show("Image file not found");
                picEditPicture.Image = null;
            }
        }

        private void btnResetEditStudent_Click(object sender, EventArgs e)
        {
            txtEditMemberID.Text = "";
            txtEditGivenName.Text = "";
            txtEditSurname.Text = "";
            dtpEditDOB.Text = "";
            cmbEditGender.Text = "Female";
            txtEditHmphone.Text = "";
            txtEditWkphone.Text = "";
            txtEditMobile.Text = "";
            txtEditEmail.Text = "";
            txtEditSkype.Text = "";
            txtEditAddress.Text = "";
            txtEditMartialArtsHistory.Text = "";
            txtEditComments.Text = "";

            chkEditAilments.Checked = false;
            txtEditMedNotes.Text = "";
            chkEditAuthorisation.Checked = false;

            txtEditEmergName.Text = "";
            txtEditEmergRelationship.Text = "";
            txtEditEmergPhone.Text = "";
            txtEditEmergMobile.Text = "";

            btnEditStudent.Visible = false;
            btnResetEditStudent.Visible = false;
            btnEditLookupID.Visible = true;
            txtEditMemberID.ReadOnly = false;
        }

        private void btnAddPhoto_Click(object sender, EventArgs e)
        {
            select_picture(txtAddPicture, picAddPicture);
        }

        private void btnEditPicture_Click(object sender, EventArgs e)
        {
            select_picture(txtEditPicture, picEditPicture);
        }

        private void select_picture(TextBox t, PictureBox p)
        {
            // open file dialog 
            OpenFileDialog open = new OpenFileDialog();
            // image filters
            open.Filter = "Image Files(*.jpg; *.jpeg; *.gif; *.bmp)|*.jpg; *.jpeg; *.gif; *.bmp";
            if (open.ShowDialog() == DialogResult.OK)
            {
                // display image in picture box
                p.Image = new Bitmap(open.FileName);
                // image file path
                t.Text = open.FileName;
            }
        }

        #endregion

        #region Classes/Grading
        private void btnAddClass_Click(object sender, EventArgs e)
        {
            int ClassID;
            bool validNumber = int.TryParse(txtAddClassID.Text, out ClassID);

            if (!validNumber)
            {
                MessageBox.Show("Class ID must be a number");
                return;
            }

            // Lookup class ID in database
            int count = Utilities.lookupClassID(ClassID);

            if (count != 0)
            {
                MessageBox.Show("Class ID: " + txtID.Text + " already exists");
            }
            else
            {
                //Check all fields are valid
                if (txtAddClassName.TextLength == 0)
                {
                    MessageBox.Show("Class must have a name.");
                    return;
                }
                if (cmbAddClassStyle.SelectedIndex == -1)
                {
                    MessageBox.Show("Class must have a style");
                    return;
                }
                if (cmbAddClassType.SelectedIndex == -1)
                {
                    MessageBox.Show("Class must have a type");
                    return;
                }
                
                Class newClass = new Class();
                newClass.AddClass(ClassID, txtAddClassName.Text, Convert.ToInt32(((ClassType)cmbAddClassType.SelectedItem).ID), dtpAddClassDate.Value.Date, dtpAddClassTime.Value, Convert.ToInt32(numAddClassLength.Value), Convert.ToInt32(((FightingStyle)cmbAddClassStyle.SelectedItem).ID));
                MessageBox.Show("Class successfully added");
            }
        }
        private void classPrepareForms()
        {
            // Lookup lists of fighting styles and class types
            DataSet fightingStyles = Utilities.SearchDatabase("SELECT ID, TYPE FROM FightingStyle_Lookup;");
            
            //this is how to read the dataset
            //if you pass multiple SELECT statements the dataset will contain multiple "Tables" hence Table[0] is the first table returned
            //each table has ROWS - Loop through each Row - hence Rows[i]
            //ItemArray.GetValue(0) - this is the value of the first column. GetValue(1) is the second column
            for (int i=0; i < fightingStyles.Tables[0].Rows.Count; i++)
            {
                FightingStyle newStyle = new FightingStyle(Convert.ToInt32(fightingStyles.Tables[0].Rows[i].ItemArray.GetValue(0).ToString()),fightingStyles.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                cmbAddClassStyle.Items.Add(newStyle);
                cmbEditClassStyle.Items.Add(newStyle);
                cmbAssignGradeStyle.Items.Add(newStyle);
            }

            DataSet classTypes = Utilities.SearchDatabase("SELECT ID, TYPE FROM ClassType_Lookup;");
            for (int i = 0; i < classTypes.Tables[0].Rows.Count; i++)
            {
                ClassType newType = new ClassType(Convert.ToInt32(classTypes.Tables[0].Rows[i].ItemArray.GetValue(0).ToString()), classTypes.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                cmbAddClassType.Items.Add(newType);
                cmbEditClassType.Items.Add(newType);
            }

            // Disable the edit class fields until a valid class is looked up
            classEditEnableControls(false);
            classMarkAttenSubmitControls(false);
            classMarkAttenValiControls(true);
            classAssignGradeControls(false);
        }
        private void classEditEnableControls(bool status)
        {
            txtEditClassName.Enabled = status;
            cmbEditClassStyle.Enabled = status;
            cmbEditClassType.Enabled = status;
            dtpEditClassDate.Enabled = status;
            dtpEditClassTime.Enabled = status;
            numEditClassLength.Enabled = status;
            btnEditClassDelete.Enabled = status;
            btnEditClassSubmit.Enabled = status;

            if (status == false)
            {
                txtEditClassID.ResetText();
                txtEditClassName.ResetText();
                cmbEditClassStyle.ResetText();
                cmbEditClassType.ResetText();
                dtpEditClassDate.ResetText();
                dtpEditClassTime.ResetText();
                numEditClassLength.ResetText();
            }
        }
        private void classMarkAttenSubmitControls(bool status)
        {
            btnMarkAttSubmit.Enabled = status;
        }
        private void classMarkAttenValiControls(bool status)
        {
            txtMarkAttClass.Enabled = status;
            txtMarkAttStudent.Enabled = status;
            btnMarkAttendanceValidate.Enabled = status;
        }
        private void classAssignGradeControls(bool status)
        {
            cmbAssignGradeStyle.Enabled = status;
            txtAssignGradeScore.Enabled = status;
            btnAssignGradeSubmit.Enabled = status;
            if (!status)
            {
                cmbAssignGradeRank.Enabled = status;
            }
        }
        private void classAssignGradeRankLookup(int style)
        {
            // style is a 1 for Judo/Jui Jitsu or a 2 for Chinese Boxing
            if (style == 1)
            {
                cmbAssignGradeRank.Items.Clear();
                cmbAssignGradeRank.ResetText();

                DataSet JJJRankLookup = Utilities.SearchDatabase("SELECT Id, Type from JJJRank_Lookup;");

                for (int i = 0; i < JJJRankLookup.Tables[0].Rows.Count; i++)
                {
                    Rank newRank = new Rank(Convert.ToInt32(JJJRankLookup.Tables[0].Rows[i].ItemArray.GetValue(0).ToString()), JJJRankLookup.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                    cmbAssignGradeRank.Items.Add(newRank);
                }
                cmbAssignGradeRank.Enabled = true;
            }
            else if (style == 2)
            {
                cmbAssignGradeRank.Items.Clear();
                cmbAssignGradeRank.ResetText();

                DataSet CBRankLookup = Utilities.SearchDatabase("SELECT ID, Type from CBRank_Lookup;");

                for (int i = 0; i < CBRankLookup.Tables[0].Rows.Count; i++)
                {
                    Rank newRank = new Rank(Convert.ToInt32(CBRankLookup.Tables[0].Rows[i].ItemArray.GetValue(0).ToString()), CBRankLookup.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                    cmbAssignGradeRank.Items.Add(newRank);
                }
                cmbAssignGradeRank.Enabled = true;
            }
            else
            {
                cmbAssignGradeRank.Items.Clear();
                cmbAssignGradeRank.Enabled = false;
            }
        }
        private void classAssignGradeValiControls(bool status)
        {
            txtAssignGradeStudent.Enabled = status;
            btnAssignGradeLookup.Enabled = status;
        }
        private void btnEditClassLookup_Click(object sender, EventArgs e)
        {
            //Check if the user has entered data into the field
            if (txtEditClassID.TextLength == 0)
            {
                MessageBox.Show("Please enter a value in the Class ID field.");
                return;
            }

            //Check if the value is a number, and convert it to a number if it is
            int ClassID;
            if (!int.TryParse(txtEditClassID.Text, out ClassID))
            {
                MessageBox.Show("Please enter a numeric value in the Class ID field.");
                return;
            }

            //Check if the number represents a class in the database and propogate the fields
            DataSet Class = Utilities.SearchDatabase(String.Format("SELECT Name, Style, Type, CONVERT(varchar(10), ClassDate, 103), ClassTime, Duration FROM CLASS WHERE ID = {0}", ClassID));
            if (Class.Tables[0].Rows.Count != 1)
            {
                MessageBox.Show("Sorry, the class could not be found. Please try again", "Class not found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            txtEditClassName.Text = Class.Tables[0].Rows[0].ItemArray.GetValue(0).ToString();
            // set class style
            foreach (FightingStyle f in cmbEditClassStyle.Items)
            {
                if (f.ID == Convert.ToInt32(Class.Tables[0].Rows[0].ItemArray.GetValue(1).ToString()))
                {
                    cmbEditClassStyle.SelectedIndex = cmbEditClassStyle.Items.IndexOf(f);
                    break;
                }
            }
            // set class type
            foreach (ClassType c in cmbEditClassType.Items)
            {
                if (c.ID == Convert.ToInt32(Class.Tables[0].Rows[0].ItemArray.GetValue(2).ToString()))
                {
                    cmbEditClassType.SelectedIndex = cmbEditClassType.Items.IndexOf(c);
                    break;
                }
            }
            dtpEditClassDate.Value = Convert.ToDateTime(Class.Tables[0].Rows[0].ItemArray.GetValue(3).ToString());
            dtpEditClassTime.Value = Convert.ToDateTime(Class.Tables[0].Rows[0].ItemArray.GetValue(4).ToString());
            numEditClassLength.Value = Convert.ToInt32(Class.Tables[0].Rows[0].ItemArray.GetValue(5).ToString());
            classEditEnableControls(true);
        }
        private void btnEditClassSubmit_Click(object sender, EventArgs e)
        {
            Class editClass = new Class();
            editClass.UpdateClass(Convert.ToInt32(txtEditClassID.Text), txtEditClassName.Text, ((ClassType)cmbEditClassType.SelectedItem).ID, dtpEditClassDate.Value.Date, dtpEditClassTime.Value, Convert.ToInt32(numEditClassLength.Value), ((FightingStyle)cmbEditClassStyle.SelectedItem).ID);
            MessageBox.Show("Class successfully updated");
            classEditEnableControls(false);
        }
        private void btnEditClassReset_Click(object sender, EventArgs e)
        {
            classEditEnableControls(false);
        }
        private void btnEditClassDelete_Click(object sender, EventArgs e)
        {
            Class deleteClass = new Class();
            deleteClass.DeleteClass(Convert.ToInt32(txtEditClassID.Text));
        }
        private void btnResetAddClass_Click(object sender, EventArgs e)
        {
            txtAddClassID.ResetText();
            txtAddClassName.ResetText();
            cmbAddClassStyle.ResetText();
            cmbAddClassType.ResetText();
            dtpAddClassDate.ResetText();
            dtpAddClassTime.ResetText();
            numAddClassLength.ResetText();
        }
        private void btnMarkAttendanceValidate_Click(object sender, EventArgs e)
        {
            //Setup a value for if all classes\students can be validated
            bool Valid = true;

            //We need an array of strings for each line in the Classes textbox
            string[] ClassesText = txtMarkAttClass.Text.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);

            //Step through each line and check if it is a valid class and add the class to the list box
            foreach (string ClassID in ClassesText)
            {
                Class tempClass = new Class();
                int i;
                if(!Int32.TryParse(ClassID, out i))
                {
                    Valid = false;
                    break;
                }

                tempClass.ClassID = i;

                if(tempClass.isValid())
                {
                    DataSet Class = Utilities.SearchDatabase(String.Format("SELECT Name FROM CLASS WHERE ID = {0}", ClassID));
                    tempClass.Name = Class.Tables[0].Rows[0].ItemArray.GetValue(0).ToString();
                    lstMarkAttClass.Items.Add(tempClass);
                    Valid = true;
                }
                else
                {
                    Valid = false;
                    break;
                }
            }

            if (!Valid)
            {
                MessageBox.Show("One or more of the classes entered is not valid. Please correct and try again");
                lstMarkAttClass.ResetText();
                return;
            }

            if (lstMarkAttClass.Items.Count == 0)
            {
                MessageBox.Show("You need to enter at least one class.");
                return;
            }

            //Setup an array of string for each line in the Student ID textbox
            string[] StudentsText = txtMarkAttStudent.Text.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);

            //Step through each line and check if it is a valid student ID and add the student to the list box
            foreach (string StudentID in StudentsText)
            {
                int i;
                if (!int.TryParse(StudentID, out i))
                {
                    Valid = false;
                    break;
                }
                else if (Utilities.lookupMemberID(i) == 1)
                {
                    Member tempStudent = new Member();
                    DataSet Student = Utilities.SearchDatabase(String.Format("SELECT GivenName, LastName FROM Member WHERE ID = {0}", StudentID));
                    tempStudent.ID = i;
                    tempStudent.GivenName = Student.Tables[0].Rows[0].ItemArray.GetValue(0).ToString();
                    tempStudent.LastName = Student.Tables[0].Rows[0].ItemArray.GetValue(1).ToString();
                    lstMarkAttStudent.Items.Add(tempStudent);
                    Valid = true;
                }
                else
                {
                    Valid = false;
                    break;
                }
            }

            if (!Valid)
            {
                MessageBox.Show("One or more of the classes entered is not valid. Please correct and try again");
                lstMarkAttClass.ResetText();
                lstMarkAttStudent.ResetText();
                return;
            }

            if (lstMarkAttStudent.Items.Count == 0)
            {
                MessageBox.Show("You need to enter atleast one student.");
                lstMarkAttClass.ResetText();
                return;
            }

            classMarkAttenSubmitControls(true);
            classMarkAttenValiControls(false);
        }
        private void btnMarkAttReset_Click(object sender, EventArgs e)
        {
            txtMarkAttClass.ResetText();
            txtMarkAttStudent.ResetText();
            lstMarkAttClass.ResetText();
            lstMarkAttStudent.ResetText();
            classMarkAttenSubmitControls(false);
            classMarkAttenValiControls(true);
        }
        private void btnMarkAttSubmit_Click(object sender, EventArgs e)
        {
            foreach (Class validClass in lstMarkAttClass.Items)
            {
                foreach (Member validMember in lstMarkAttStudent.Items)
                {
                    // Check if the member has registration paid up and had class passes available
                    bool valid = true;
                    if (valid)
                    {
                        Utilities.DatabaseNonQuery(string.Format("INSERT into AttendClass (ID, Member_ID,Class_ID) SELECT ISNULL(MAX(ID) + 1, 1), {0}, {1} FROM AttendClass", validMember.ID, validClass.ClassID));
                        MessageBox.Show("Class attendance saved");
                    }
                }
            }
        }
        private void cmbAssignGradeStyle_ChangeCommitted(object sender, EventArgs e)
        {
            if ((((FightingStyle)cmbAssignGradeStyle.SelectedItem).ID == 1) || (((FightingStyle)cmbAssignGradeStyle.SelectedItem).ID ==2))
            {
                classAssignGradeRankLookup(1);
                cmbAssignGradeRank.Enabled = true;
            }
            else if (((FightingStyle)cmbAssignGradeStyle.SelectedItem).ID == 3)
            {
                classAssignGradeRankLookup(2);
                cmbAssignGradeRank.Enabled = true;
            }
        }
        private void btnAssignGradeLookup_Click(object sender, EventArgs e)
        {
            int i;
            bool Valid = false;
            if(!Int32.TryParse(txtAssignGradeStudent.Text, out i))
            {
                Valid = false;
            }
            else if (Utilities.lookupMemberID(i)==1)
            {
                Member tempStudent = new Member();
                DataSet Student = Utilities.SearchDatabase(String.Format("Select GivenName, LastName FROM Member WHERE ID = {0}",i));
                tempStudent.ID=i;
                tempStudent.GivenName = Student.Tables[0].Rows[0].ItemArray.GetValue(0).ToString();
                tempStudent.LastName = Student.Tables[0].Rows[0].ItemArray.GetValue(1).ToString();
                lstAssignGradeStudent.Items.Add(tempStudent);
                classAssignGradeControls(true);
                classAssignGradeValiControls(false);
                Valid = true;
            }
            else
            {
                Valid = false;
            }
               
            if(!Valid)
            {
                MessageBox.Show("Please enter a valid Student ID and try again.");
                txtAssignGradeStudent.Clear();
                lstAssignGradeStudent.Items.Clear();
                classAssignGradeValiControls(true);
                classAssignGradeControls(false);
            }
        }
        private void btnAssignGradeReset_Click(object sender, EventArgs e)
        {
            classAssignGradeControls(false);
            classAssignGradeValiControls(true);

            txtAssignGradeStudent.Clear();
            lstAssignGradeStudent.Items.Clear();
            cmbAssignGradeRank.Items.Clear();
            cmbAssignGradeRank.ResetText();
            cmbAssignGradeStyle.ResetText();
            txtAssignGradeScore.Clear();
        }
        private void btnAssignGradeSubmit_Click(object sender, EventArgs e)
        {
            int gradeID;
            DataSet CountID = Utilities.SearchDatabase("SELECT COUNT(ID) FROM Grading");
            if ((Convert.ToInt32(CountID.Tables[0].Rows[0].ItemArray.GetValue(0).ToString())) > 0)
            {
                DataSet MaxID = Utilities.SearchDatabase("SELECT MAX(ID) FROM Grading");
                gradeID = Convert.ToInt32(MaxID.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());
                gradeID++;
            }
            else
            {
                gradeID = 0;
            }

            Utilities.DatabaseNonQuery(string.Format("INSERT INTO grading (ID, FightingStyle, RankAttained, GradingDate, GradingScore, Member_ID) VALUES ('{0}','{1}','{2}',CONVERT(datetime, '{3}', 103),'{4}','{5}')", gradeID.ToString(), ((FightingStyle)cmbAssignGradeStyle.SelectedItem).ID, ((Rank)cmbAssignGradeRank.SelectedItem).ID, DateTime.Today.ToString(), txtAssignGradeScore.Text, txtAssignGradeStudent.Text));
            MessageBox.Show("Grade Saved");
            classAssignGradeControls(false);
            classAssignGradeValiControls(true);
            txtAssignGradeStudent.ResetText();
            lstAssignGradeStudent.Items.Clear();
            cmbAssignGradeStyle.ResetText();
            cmbAssignGradeRank.Items.Clear();
            cmbAssignGradeRank.ResetText();
            txtAssignGradeScore.ResetText();
        }
        #endregion

        #region Purchasing
        //the line items that are to be purchased
        List<LineItem> _lineItems = new List<LineItem>();

        private void btnPurchAddItems_Click(object sender, EventArgs e)
        {

            Membership membReg = new Membership();
            Product prod = new Product();
            TrainingPass trainPass = new TrainingPass();
            
            StringBuilder sb = new StringBuilder();

            //in the case where items are added, then decided more items to be added, the _lineitems already 
            //contains items, so remove the items from the list view otherwise they will double up.
            lsvPurchItems.Items.Clear();

            //variable to tally the sub total
            double dblSubTotal = 0;

            Form frmMainForm = this;
            frmPurchaseItems frmPurchase = new frmPurchaseItems(_lineItems,frmMainForm);
            frmPurchase.ShowDialog();
            //Reference: http://msdn.microsoft.com/en-us/library/c7ykbedk.aspx Retrieved: 21/09/2012
            
            //receive _lineItems LIST - the list of products being purchased.
            foreach (var item in _lineItems)
            {
                switch (item.Type.ToString().ToUpper())
                {
                    case "MEMBERSHIP":
                         membReg = (Membership)item.Item;
                        //reset the stringbuilder
                        sb.Clear();
                        //build the string from the Line Items, to display in the list box
                        sb.Append("Academy: " + membReg.AffiliatedAcademy.AcademyName + ", " );
                        sb.Append(membReg.MembershipType + " | Expires: ");
                        sb.Append(membReg.MembershipExpiry.ToShortDateString());
                        
                        //create a list item to display in the list view "lsvPurchItems"
                        ListViewItem newListMemb = new ListViewItem(item.Type);
                        //add items to each column
                        newListMemb.SubItems.Add(sb.ToString());
                        newListMemb.SubItems.Add("$ " + membReg.RegistrationFee.FeeAmount.ToString());
                        //add the list item to the list view
                        lsvPurchItems.Items.Add(newListMemb);
                        //Reference: http://stackoverflow.com/questions/473148/c-sharp-listview-how-do-i-add-items-to-columns-2-3-and-4-etc
                        
                        //Auto add up the Amount Paid and display total in txtPurchSubTotal
                        dblSubTotal += (double)membReg.RegistrationFee.FeeAmount;
                        break;
                    case "PRODUCT":
                        prod = (Product)item.Item;
                        //reset the stringbuilder
                        sb.Clear();
                        //build the string from the Line Items, to display in the list box
                        sb.Append("Qty: " + prod.Quantity.ToString() + " ");
                        sb.Append(prod.ProductName.ToString());

                        //create a list item to display in the list view "lsvPurchItems"
                        ListViewItem newListProd = new ListViewItem(item.Type);
                        //add items to each column
                        newListProd.SubItems.Add(sb.ToString());
                        newListProd.SubItems.Add("$ " + prod.PriceExGST.ToString());
                        //add the list item to the list view
                        lsvPurchItems.Items.Add(newListProd);
                        //Reference: http://stackoverflow.com/questions/473148/c-sharp-listview-how-do-i-add-items-to-columns-2-3-and-4-etc

                        //Auto add up the Amount Paid and display total in txtPurchSubTotal
                        dblSubTotal += (double)prod.PriceExGST;
                        break;
                    case "TRAININGPASS":
                        trainPass = (TrainingPass)item.Item;
                        //reset the stringbuilder
                        sb.Clear();
                        //build the string from the Line Item, to display in the list box
                        sb.Append("Pass Type: " + trainPass.TrainingPassType.PassTypeName + " ");
                        //sb.Append(

                        //create a list item to display in the list view "lsvPurchItems"
                        ListViewItem newListPass = new ListViewItem(item.Type);
                        //add items to each column
                        newListPass.SubItems.Add(sb.ToString());
                        newListPass.SubItems.Add("$ " + trainPass.FeePaid.ToString());
                        //add the list item to the list view
                        lsvPurchItems.Items.Add(newListPass);
                        //Reference: http://stackoverflow.com/questions/473148/c-sharp-listview-how-do-i-add-items-to-columns-2-3-and-4-etc

                        //Auto add up the Amount Paid and display total in txtPurchSubTotal
                        dblSubTotal += (double)trainPass.FeePaid;
                        break;
                    default:
                        break;
                }               
            }

            //Add the sub total to the subtotal textbox
            double dblSub = addSubTotal();
            txtPurchSubTotal.Text = "$ " + dblSub.ToString();
            //calculate and display the Total
            txtPurchTotal.Text = "$ " + Math.Round(calcTotal(dblSub), 2).ToString(); 
        }
                
        private void btnPurchLookupMember_Click(object sender, EventArgs e)
        {
            //search for Member ID or Member Name
            if (txtPurchMemberID.Text == "" && txtPurchMemberName.Text == "")
            {
                MessageBox.Show("Please enter a value in either the Membership ID or Member Name.");
                return;
            }

            if (txtPurchMemberID.Text != "")
            {
               //lookup memberid in the DB
                purchMemberIDSearch();
            }
            else if (txtPurchMemberName.Text != "")
            {
                //lookup member name in the DB
                purchMemberNameSearch();
            }      
        }

        private void btnPurchClearSearch_Click(object sender, EventArgs e)
        {
            //clear the search for a new search
            purchClearForm();
        }

        private void purchClearForm()
        {
            //clear the search for a new search
            txtPurchMemberID.Text = "";
            txtPurchMemberName.Text = "";
            lsvPurchItems.Items.Clear();
            purchEnableControls(false);
            //update the payment method
            purchEnableChequePayment();
        }

        private void btnPurchDeleteItem_Click(object sender, EventArgs e)
        {
            

            //test that an item has been selected
            if (lsvPurchItems.SelectedIndices.Count == 0)
            {
                //message box that an item needs to be selcted.
                MessageBox.Show("Please select the required item to be removed.", "Delete Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                //exit the function
                return;
            }
            //remove the selected item from the list box
            int index;
            foreach (ListViewItem item in  lsvPurchItems.Items)
            {
                //only one item can be selected, as set by the properties of the listview box
                if (item.Selected)
                {
                    //set the index variable to use to remove the item from the _list
                    index = item.Index;
                    try
                    {
                        //prompt for confirmation of Delete
                        DialogResult result = MessageBox.Show(this, "Are you sure you want to remove: " + item.SubItems[0].Text + " " + item.SubItems[1].Text + " from " + txtPurchMemberName.Text + "'s Order?", "Remove Item", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                        if (result == DialogResult.Yes)
                        {
                            //remove the select item from the listview box
                            lsvPurchItems.Items.RemoveAt(index);
                            //remove the selected item from the _item list - should be the same index as the selected item in the listbox
                            _lineItems.RemoveAt(index);
                            //recalculate the subtotal and Total
                            //Add the sub total to the subtotal textbox
                            double dblSub = addSubTotal();
                            txtPurchSubTotal.Text = "$ " + dblSub.ToString();
                            //calculate and display the Total
                            txtPurchTotal.Text = "$ " + Math.Round(calcTotal(dblSub), 2).ToString(); 
                            //update the payment method
                            purchEnableChequePayment();
                            //only one item can be selected, so we don't need to test for any more selected items return out of this function - (developers decision, for simplicity)
                            return;
                        }
                       
                    }
                    catch (Exception)
                    {
                        //message box that an error has occurred
                        MessageBox.Show("An error has occurred, please try again.", "Delete Item", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //exit the function
                        return;
                    }
                }                
            }
            

            //recalculate total
            addSubTotal();
        }

        private void ddlPurchPaymentMethod_SelectedIndexChanged(object sender, EventArgs e)
        {
            purchEnableChequePayment();
        }

        private double addSubTotal()
        {

            double dblSubTotal = 0;

            //loop through each list item, get each Amount, make a double data type, add together, insert into subtotal txtbox
            foreach (ListViewItem item in lsvPurchItems.Items)
            {
                ListViewItem lst = new ListViewItem();
                lst = item;

                string str = lst.SubItems[lsvPurchItems.Columns.Count - 1].Text.ToString();//the last column is the Amount column

                //strip the "$ " from the item, get the 'last' item, and remove any spaces
                str = str.Split(new char[] { '$' }, StringSplitOptions.RemoveEmptyEntries).Last().Trim();

                //convert str to a double data type and add each item together
                dblSubTotal += double.Parse(str);
            }

            //return the result 'dblSubTotal'
            return dblSubTotal;
        }
        private double calcTotal(double subTotal)
        {
            //calculate the total by multiplying the sub total with the GST text box
            double total = 0;
            double GST = double.Parse(txtPurchGST.Text) / 100; //get the amount and div by 100 for the percent

            total = subTotal * GST + subTotal;

            return total;
        }
        private void purchMemberIDSearch()
        {
            //lookup memberID in the database
            int MemberID;
            bool validNumber = int.TryParse(txtPurchMemberID.Text, out MemberID);

            if (!validNumber)
            {
                MessageBox.Show("Member ID must be a number");
                return;
            }

            string sqlCountMembershipID = "SELECT COUNT(*) FROM Member WHERE ID LIKE '%{0}%';";
            string sqlSelectMembershipID = "SELECT * FROM Member WHERE ID LIKE '%{0}%';";

            string sqlConcat = string.Format(sqlCountMembershipID + sqlSelectMembershipID, txtPurchMemberID.Text);
            DataSet dsSearchMemberID = Utilities.SearchDatabase(sqlConcat);
            int count = int.Parse(dsSearchMemberID.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());

            //if member found, controls become enabled
            if (count >= 1)
            {//show Selectmember dialog as there is one or more members matching the search criteria

                Member _member = new Member();

                SelectMember frmSelectMember = new SelectMember(_member, dsSearchMemberID, "ID");//create a SelectMember form object, passing in the _member & dsSearchName variables
                //show the SelectMember form (as a Dialog form - can't click on other forms until this closed)
                frmSelectMember.ShowDialog();

                if (_member.ID == 0)
                {
                    //No member selected, or Cancel button clicked
                    //exit the function - to prevent incorrect data being displayed e.g. _member.ID would return a '0'
                    return;
                }
                txtPurchMemberID.Text = _member.ID.ToString();//fill in the member id text box
                txtPurchMemberName.Text = _member.GivenName + " " + _member.LastName; //fill in the member text box

                //enable the form to add items etc
                purchEnableControls(true);
            }
            else//else popup error message (messagebox)
            {
                MessageBox.Show("Sorry, the member could not be found. Please try again", "Member not found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                purchEnableControls(false);
            }
        }
        private void purchMemberNameSearch()
        {
            //search the database for a member's name (DB table name = Member, Fields = GivenName, LastName )

            //get the count of how many with the same GivenName, if more than one, open the "select member form"
            string sqlCountGivenName = "SELECT COUNT(*) FROM Member WHERE GivenName LIKE '%{0}%';";
            string sqlCountLastName = "SELECT COUNT(*) FROM Member WHERE LastName LIKE '%{0}%';";
            string sqlSearchGivenName = "SELECT * FROM Member WHERE GivenName LIKE '%{0}%'; ";
            string sqlSearchLastName = "SELECT * FROM Member WHERE LastName LIKE '%{0}%';";
            string sqlConcat = string.Format(sqlCountGivenName + sqlCountLastName + sqlSearchGivenName + sqlSearchLastName, txtPurchMemberName.Text);

            //DataSet (dtSearchName) will have four tables within the results
            //one for each SQL statement above
            //Table[0] - is the count of how many members with the given name
            //Table[1] - is the count of how many members with the searched for last name
            //Table[2] - is the result of every member with the searched for given name
            //Table[3] - is the result of every member with the searched for last name
            DataSet dsSearchName = Utilities.SearchDatabase(sqlConcat);


            //get the count of Given Names and Last names by adding the result from table[0] and table[1]
            int count = int.Parse(dsSearchName.Tables[0].Rows[0].ItemArray.GetValue(0).ToString()) + int.Parse(dsSearchName.Tables[1].Rows[0].ItemArray.GetValue(0).ToString());

            //if member found, controls become enabled
            if (count >= 1)
            {//show Selectmember dialog as there is one or more members matching the search criteria

                Member _member = new Member();

                SelectMember frmSelectMember = new SelectMember(_member, dsSearchName, "NAME");//create a SelectMember form object, passing in the _member & dsSearchName variables
                //show the SelectMember form (as a Dialog form - can't click on other forms until this closed)
                frmSelectMember.ShowDialog();
                if (_member.ID == 0)
                {
                    //No member selected, or Cancel button clicked
                    //exit the function - to prevent incorrect data being displayed e.g. _member.ID would return a '0'
                    return;
                }
                txtPurchMemberID.Text = _member.ID.ToString();//fill in the member id text box
                txtPurchMemberName.Text = _member.GivenName + " " + _member.LastName; //fill in the member text box

                //enable the form to add items etc
                purchEnableControls(true);
            }
            else
            { //no members found

                MessageBox.Show("No members where found, please check the spelling and try again", "No results found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                //reset the form
                txtPurchMemberName.Text = "";
                txtPurchMemberID.Text = "";

                //ensure that items can't be 'ordered'
                purchEnableControls(false);
            }


        }
        private void purchEnableControls(bool status)
        {
            btnPurchAddItems.Enabled = status;
            btnPurchDeleteItem.Enabled = status;
            lsvPurchItems.Enabled = status;
            ddlPurchPaymentMethod.Enabled = status;
            ddlPurchPaymentMethod.SelectedIndex = 0;
            btnPurchProcessOrder.Enabled = status;
        }
        private void purchEnableChequePayment()
        {
            bool status = false;

            if (ddlPurchPaymentMethod.SelectedItem.ToString() == "Cheque")
            {
                status = true;
                txtChequeAmount.Text = txtPurchTotal.Text;
            }
            else
            {
                status = false;
                txtChequeAmount.Text = "";
            }

            //Enable/Disable the cheque detail text boxes.
            txtChequeNumber.Enabled = status;
            txtChequeAmount.Enabled = status;
            txtChequeDrawerName.Enabled = status;
            txtChequeBank.Enabled = status;
        }
                
        private bool purchProcessButtonValidation()
        {

            bool isValid = false;
            int errorCount = 0;
            string strErrors = "Please review the following field(s):\r\n\r\n";
            //make sure there are items in the list to process
            if (lsvPurchItems.Items.Count < 1)
            {
                strErrors += "Please select some items to purchase by clicking the Add Items button. ";
                errorCount++;
            }

            if (ddlPurchPaymentMethod.SelectedItem.ToString().Contains("Select"))
            {
                strErrors += "\r\nPlease select a payment method.";
                errorCount++; 
            }

            if (ddlPurchPaymentMethod.SelectedItem.ToString().Contains("Cheque"))
            {
                if (txtChequeNumber.Text == "")
                {
                    strErrors += "\r\nPlease enter the Cheque Number.";
                    errorCount++;
                }
                
                if (txtChequeAmount.Text == "")
                {
                    strErrors += "\r\nPlease enter the Cheque Amount.";
                    errorCount++; 
                }
                
                if (txtChequeDrawerName.Text == "")
                {
                   strErrors += "\r\nPlease enter the Name of the Drawer for the Cheque.";
                    errorCount++; 
                }
                
                if (txtChequeBank.Text == "")
                {
                   strErrors += "\r\nPlease enter the Cheque's Bank name.";
                    errorCount++; 
                }
            }

            if (errorCount > 0) //i.e. there are some fileds missing data
            {
                MessageBox.Show(strErrors, "Required Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                isValid = false;
            }
            else
                isValid = true;


            return isValid;
        }

        private void btnPurchProcessOrder_Click(object sender, EventArgs e)
        {
            //if validation fails exit this event
            if (!purchProcessButtonValidation())
                return;
            
            //create a transaction as this inserts into multiple tables, if one
            //Insert fails the transaction is rolled back, keeping the db in a consistent state
            //Taken from: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.90).aspx
            try
            {
                using (TransactionScope scope = new TransactionScope())
                {

                    int intChequeID = -1;

                    //if paying by cheque, fill the cheque class and fill the db
                    #region Paying by cheque - insert to db and retrieve ID for TransactionDetails
                    if (ddlPurchPaymentMethod.SelectedItem.ToString().Contains("Cheque"))
                    {
                        Cheque chq = new Cheque();

                        string[] chqAmount = txtChequeAmount.Text.Split(new string[] { "$ " }, StringSplitOptions.RemoveEmptyEntries);
                        //fill chq details
                        chq.Amount = double.Parse(chqAmount[0]);
                        chq.Bank = txtChequeBank.Text;
                        chq.ChequeNumber = txtChequeNumber.Text;
                        chq.Drawer = txtChequeDrawerName.Text;
                        chq.AddCheque(ref intChequeID);
                    }
                    #endregion

                    //insert the transaction detail
                    TransactionDetails tranDetails = new TransactionDetails();
                    int intTranDetails = -1;
                    tranDetails.PaymentDate = DateTime.Today;
                    tranDetails.ReceivedFrom = txtPurchMemberName.Text;
                    string[] totalAmount = txtPurchTotal.Text.Split(new string[] { "$ " }, StringSplitOptions.RemoveEmptyEntries);
                    tranDetails.Total = double.Parse(totalAmount[0]);
                    tranDetails.PaymentMethod = ddlPurchPaymentMethod.SelectedItem.ToString();
                    tranDetails.MemberID = int.Parse(txtPurchMemberID.Text);
                    if (intChequeID != -1)
                    {
                        tranDetails.ChequeID = intChequeID;
                    }
                    tranDetails.AddTransaction(ref intTranDetails);

                    //variable to be inserted into LineItem {ProductID, TrainingPassID, RegistrationFeeID}
                    int intRegFeeID = -1;
                    int intProdID = -1;
                    int intTrainPassID = -1;
                    string sqlInsertStart = "INSERT INTO LineItem (ID, Quantity,Price,";
                    string sqlInsertMid = ",TransactionDetails_ID) VALUES('{0}','{1}','{2}','";
                    string sqlInsertEnd = "','{3}');";

                    foreach (LineItem item in _lineItems)
                    {
                        #region Insert 'types' into their respective tables - and retrieving the IDs to insert into the Line Item table
                        //loop through the list of items, find out the item's type, and insert into the items type table.
                        //e.g. if the item is a training pass insert it into the training pass table, get the ID then insert into the LineItem table
                        switch (item.Type.ToUpper())
                        {
                            case "MEMBERSHIP":
                                //get the type of the "item"'s ITEM - in this case is a membership object
                                Membership mem = (Membership)item.Item;

                                //will need to INSERT into the RegistrationFee Table first and get the ID                        
                                RegistrationFee regFee = mem.RegistrationFee;
                                regFee.MakePayment(ref intRegFeeID);

                                int intMembershipID = -1;
                                mem.MemberID = int.Parse(txtPurchMemberID.Text);
                                mem.AddMembershipDetails(ref intMembershipID, intRegFeeID);

                                //insert the 'item' into the LineItem table
                                string strInsertMembership;
                                strInsertMembership = sqlInsertStart + "RegistrationFee_ID" + sqlInsertMid + intRegFeeID + sqlInsertEnd;
                                item.AddItem(intTranDetails, strInsertMembership);
                                break;
                            case "PRODUCT":
                                //for product we just need the Product ID out of the item object
                                //this will get inserted in the LineItem Table
                                //get the type of the "item"'s ITEM - in this case is a Product object
                                Product prod = (Product)item.Item;
                                intProdID = prod.ProductNumber; //the ID of the product purchsed to be inserted in the LineItem table.

                                //insert the 'item' into the LineItem table
                                string strInsertProduct;
                                strInsertProduct = sqlInsertStart + "Product_ID" + sqlInsertMid + intProdID + sqlInsertEnd;
                                item.AddItem(intTranDetails, strInsertProduct);
                                break;
                            case "TRAININGPASS":
                                //get the type of the "item"'s ITEM - in this case it is a Training Pass object
                                TrainingPass trainPass = (TrainingPass)item.Item;
                                //insert the training Pass into the DB - 
                                //and get the ID of the Training to insert into each member
                                trainPass.AddPass(ref intTrainPassID);

                                //for each member on the training pass insert the Training pass ID into each Member's TrainingPass_ID
                                List<Member> memList = trainPass.MembersOnTrainingPass;
                                int i = 0;
                                foreach (Member member in memList)
                                {
                                    //update each member in the database by inserting the TrainingPassID
                                    member.InsertTrainingPassID(intTrainPassID);
                                    i++;
                                }
                                //insert the 'item' into the LineItem table
                                string strInsertTrainPass;
                                strInsertTrainPass = sqlInsertStart + "TrainingPass_ID" + sqlInsertMid + intTrainPassID + sqlInsertEnd;
                                item.AddItem(intTranDetails, strInsertTrainPass);

                                break;
                            default:
                                break;
                        }
                        #endregion
                    }

                    // The Complete method commits the transaction. If an exception has been thrown, 
                    // Complete is not  called and the transaction is rolled back.
                    scope.Complete();
                }          
            }
            catch (TransactionAbortedException ex)
            {
                MessageBox.Show(string.Format("TransactionAbortedException Message: {0}", ex.Message),"DB Error",MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (ApplicationException ex)
            {
                MessageBox.Show(string.Format("ApplicationException Message: {0}", ex.Message), "DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            //display success/fail message
            MessageBox.Show("Purchase for:\r\n\r\nName: " + txtPurchMemberName.Text + "\r\nAmount: $" + txtPurchTotal.Text + "\r\n\r\nWas successful!", "Purchase Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);

            //clear form to start again
            purchClearForm();
        }

        
        #endregion

        #region SQL Reporting

        private void btnRunReport_Click(object sender, EventArgs e)
        {
            string report_headings = "Member ID, Title, GivenName, LastName, Gender, DOB, Hmphone, Wkphone, Mobile, Email, Skype, MartialArtsHistory, Comments, Address, Emergency Contact, Emergency Relationship, Emergency Telephone, Emergency Mobile, Ailments Checklist, Medical Authorisation, Medical Notes";
            string report_name = "All_Members";

            string sqlQuery = "SELECT        Member.ID AS [Member ID], Member.Title, Member.GivenName, Member.LastName, Member.Gender, Member.DOB, Member.Hmphone, Member.Wkphone, " +
                               "Member.Mobile, Member.Email, Member.Skype, Member.MartialArtsHistory, Member.Comments, Member.Address,  " +
                               "EmergencyContact.Name AS [Emergency Contact], EmergencyContact.Relationship AS [Emergency Relationship],  " +
                               "EmergencyContact.Telephone AS [Emergency Telephone], EmergencyContact.Mobile AS [Emergency Mobile], MedicalHistory.AilmentsChecklist,  " +
                               "MedicalHistory.MedicalAuthorisation, MedicalHistory.Notes AS [Medical Notes] " +
                               "FROM            Member LEFT OUTER JOIN " +
                               "EmergencyContact ON Member.Emergencycontact_ID = EmergencyContact.ID LEFT OUTER JOIN " +
                               "MedicalHistory ON Member.Medicalhistory_ID = MedicalHistory.ID ";

            Utilities.CreateSqlReport(sqlQuery, report_headings, report_name);

        }

        private void btnRunReport_MI_Click(object sender, EventArgs e)
        {
            string report_headings = "Member ID, Member Name, Transaction ID, Payment Date, Payment Total, Payment Method";
            string report_name = "Member_Invoices";

            string member_id;
            if (txtRepMI_MemberID.Text == "")
                member_id = "mem.id";
            else
                member_id = txtRepMI_MemberID.Text;

            string sqlQuery = string.Format("SELECT mem.id, mem.GivenName + \'  \' + mem.LastName, trd.id, trd.PaymentDate, trd.Total, trd.PaymentMethod FROM TransactionDetails trd, Member mem WHERE trd.member_id = {0} AND trd.member_id = mem.id AND trd.PaymentDate > CONVERT(datetime, '{1}', 103) AND trd.PaymentDate < CONVERT(datetime, '{2}', 103)", member_id, dtpRepMI_LowerDate.Value.Date, dtpRepMI_UpperDate.Value.Date);

            Utilities.CreateSqlReport(sqlQuery, report_headings, report_name);
        }

        private void btnRunReport_TP_Click(object sender, EventArgs e)
        {
            string report_headings = "Product ID, Product Name, Total Purchased, Total Price";
            string report_name = "Total Product Purchase";
            string product_id;

            if (txtRepTP_ProductID.Text == "")
                product_id = "prd.ID";
            else
                product_id = txtRepTP_ProductID.Text;


            string sqlQuery = string.Format("SELECT prd.id, prd.name, sum_qty.qty, sum_qty.price " +
                                            "FROM   product prd, " +
                                                   "(SELECT  SUM(lin.quantity) qty, " +
                                                            "SUM(lin.price)  price, " +
                                                            "prd.id prd_id " +
                                                    "FROM    Product prd, " +
                                                            "Lineitem lin, " +
                                                            "transactiondetails trd " +
                                                    "WHERE    prd.id = lin.product_id  " +
                                                    "AND      trd.id = lin.transactiondetails_id " +
                                                    "AND      trd.paymentdate > CONVERT(datetime, '{0}', 103) " +
                                                    "AND      trd.paymentdate < CONVERT(datetime, '{1}', 103) " +
                                                    "AND      prd.id = {2} " +
                                                    "GROUP BY lin.product_id, prd.id) sum_qty " +
                                            "WHERE prd.id = sum_qty.prd_id", dtpRepTP_LowerDate.Value.Date, dtpRepTP_UpperDate.Value.Date, product_id);

            Utilities.CreateSqlReport(sqlQuery, report_headings, report_name);
        }

        private void btnRunReport_PP_Click(object sender, EventArgs e)
        {
            string report_headings = "Product ID, Product Name, Quanitity, Date, Member ID, Member Name";
            string report_name = "Product_Purchases";
            string product_id;

            if (txtRepPP_ProductID.Text == "")
                product_id = "Product.ID";
            else
                product_id = txtRepPP_ProductID.Text;


            string sqlQuery = string.Format("SELECT        Product.ID, Product.Name, LineItem.Quantity, TransactionDetails.PaymentDate, Member.ID AS member_id, Member.GivenName + \' \' + Member.LastName " +
                                "FROM            Member INNER JOIN " +
                                "TransactionDetails ON Member.ID = TransactionDetails.Member_ID INNER JOIN " +
                                "LineItem ON TransactionDetails.ID = LineItem.TransactionDetails_ID INNER JOIN " +
                                "Product ON LineItem.Product_ID = Product.ID " +
                                "WHERE        Product.ID = {0}", product_id);

            Utilities.CreateSqlReport(sqlQuery, report_headings, report_name);


        }

        private void btnRepMP_RunReport_Click(object sender, EventArgs e)
        {
            string report_headings = "Member ID, Member Name, Qty, Price, Product Name, Training Pass Type, Registration Fee Expiry Date";
            string report_name = "Member_Purchases";

            string member_id;
            if (txtRepMP_MemberID.Text == "")
                member_id = "Member.ID";
            else
                member_id = txtRepMP_MemberID.Text;

            string sqlQuery = string.Format("SELECT        Member.ID, Member.GivenName + \' \' + Member.LastName, LineItem.Quantity, LineItem.Price, Product.Name, PassType_Lookup.TYPE, RegistrationFee.ExpiryDate " +
                                            "FROM            PassType_Lookup INNER JOIN " +
                                            "TrainingPass ON PassType_Lookup.ID = TrainingPass.PassType_ID RIGHT OUTER JOIN " +
                                            "Member INNER JOIN " +
                                            "TransactionDetails ON Member.ID = TransactionDetails.Member_ID INNER JOIN " +
                                            "LineItem ON TransactionDetails.ID = LineItem.TransactionDetails_ID LEFT OUTER JOIN " +
                                            "Product ON LineItem.Product_ID = Product.ID ON TrainingPass.ID = LineItem.TrainingPass_ID LEFT OUTER JOIN " +
                                            "RegistrationFee ON LineItem.RegistrationFee_ID = RegistrationFee.ID " +
                                            "WHERE Member.ID = {0}", member_id);

            Utilities.CreateSqlReport(sqlQuery, report_headings, report_name);

        }

        #endregion    

        private void ddlToAdminister_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Items in ddl: Select ->, Affiliated Academy, Membership Type, Training Pass Type, Staff Member, Products

            //items should be disabled
            EnableControls(false);
            setupPurchasingAdmin();

            //on selection - enable required controls, change lables, lookup existing items in db, and fill teh list view
            switch (ddlToAdminister.SelectedItem.ToString().ToUpper())
            {
                case "AFFILIATED ACADEMY": //Academy only has two required fields - ID & Type (Name)
                    //Hide not needed controls
                    chkPurchAdminIsAdmin.Visible = false;
                    lblPurchAdminFee.Visible = false;
                    lblPurchAdminFee.Text = "Amount (Ex.GST)";
                    txtPurchAdminFee.Visible = false;
                    lblPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Text = "";
                    //ensure required controls are visible and enabled
                    panel1.Enabled = true;
                    lsvAdmin.Enabled = true;
                    txtPurchAdminID.Enabled = true;
                    txtPurchAdminID.Visible = true;
                    txtPurchAdminID.Text = "";
                    txtPurchAdminName.Enabled = true;
                    txtPurchAdminName.Visible = true;
                    txtPurchAdminName.Text = "";
                    btnPurchAdminDelete.Enabled = true;
                    btnPurchAdminSelect.Enabled = true;
                    btnPurchAdminSubmit.Enabled = true;
                    //Lookup all Academys in the DB
                    string sqlSelectAcademy = "SELECT * FROM Academy_Lookup;";
                    DataSet dsAcademy = Utilities.SearchDatabase(sqlSelectAcademy);
                    //Clear columns in list view
                    lsvAdmin.Clear();
                    lsvAdmin.Columns.Add("ID").Width = 50;
                    lsvAdmin.Columns.Add("Academy").Width = 300;
                    //add results to lsvAdmin     
                    for (int i = 0; i < dsAcademy.Tables[0].Rows.Count; i++)
                    {

                        //create a list item to display in the list view "lsvAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsAcademy.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsAcademy.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        //add the new item to the list view (lsvAdmin)
                        lsvAdmin.Items.Add(newItem);
                    }
                    

                    break;
                case "MEMBERSHIP TYPE":
                    //Hide not needed controls
                    chkPurchAdminIsAdmin.Visible = false;                    
                    lblPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Text = "";
                    //ensure required controls are visible and enabled
                    panel1.Enabled = true;
                    lsvAdmin.Enabled = true;
                    txtPurchAdminID.Enabled = true;
                    txtPurchAdminID.Visible = true;
                    txtPurchAdminID.Text = "";
                    txtPurchAdminName.Enabled = true;
                    txtPurchAdminName.Visible = true;
                    txtPurchAdminName.Text = "";
                    lblPurchAdminFee.Visible = true;
                    lblPurchAdminFee.Text = "Amount (Ex.GST)";
                    txtPurchAdminFee.Visible = true;
                    txtPurchAdminFee.Enabled = true;
                    txtPurchAdminFee.Text = "";
                    btnPurchAdminDelete.Enabled = true;
                    btnPurchAdminSelect.Enabled = true;
                    btnPurchAdminSubmit.Enabled = true;
                    //Lookup all Academys in the DB
                    string sqlSelectMembership = "SELECT * FROM MembershipType_Lookup;";
                    DataSet dsMembershipType = Utilities.SearchDatabase(sqlSelectMembership);
                    //Clear columns in list view
                    lsvAdmin.Clear();
                    lsvAdmin.Columns.Add("ID").Width = 50;
                    lsvAdmin.Columns.Add("Membership Type").Width = 200;
                    lsvAdmin.Columns.Add("Price $").Width = 100;
                    //add results to lsvAdmin     
                    for (int i = 0; i < dsMembershipType.Tables[0].Rows.Count; i++)
                    {

                        //create a list item to display in the list view "lsvAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsMembershipType.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsMembershipType.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        newItem.SubItems.Add(dsMembershipType.Tables[0].Rows[i].ItemArray.GetValue(2).ToString());
                        //add the new item to the list view (lsvAdmin)
                        lsvAdmin.Items.Add(newItem);
                    }
                    break;
                case "TRAINING PASS TYPE":
                    //Hide not needed controls
                    chkPurchAdminIsAdmin.Visible = false;                    
                    //ensure required controls are visible and enabled
                    panel1.Enabled = true;
                    lsvAdmin.Enabled = true;
                    txtPurchAdminID.Enabled = true;
                    txtPurchAdminID.Visible = true;
                    txtPurchAdminID.Text = "";
                    txtPurchAdminName.Enabled = true;
                    txtPurchAdminName.Visible = true;
                    txtPurchAdminName.Text = "";
                    lblPurchAdminFee.Visible = true;
                    lblPurchAdminFee.Text = "Amount (Ex.GST)";
                    txtPurchAdminFee.Visible = true;
                    txtPurchAdminFee.Enabled = true;
                    txtPurchAdminFee.Text = "";
                    lblPurchAdminNumClasses.Visible = true;
                    txtPurchAdminNumClasses.Visible = true;
                    txtPurchAdminNumClasses.Text = "";
                    txtPurchAdminNumClasses.Enabled = true;
                    btnPurchAdminDelete.Enabled = true;
                    btnPurchAdminSelect.Enabled = true;
                    btnPurchAdminSubmit.Enabled = true;
                    //Lookup all Academys in the DB
                    string sqlSelectTrainingPass = "SELECT * FROM PassType_Lookup;";
                    DataSet dsPassType = Utilities.SearchDatabase(sqlSelectTrainingPass);
                    //Clear columns in list view
                    lsvAdmin.Clear();
                    lsvAdmin.Columns.Add("ID").Width = 50;
                    lsvAdmin.Columns.Add("Membership Type").Width = 200;
                    lsvAdmin.Columns.Add("Price $").Width = 100;
                    lsvAdmin.Columns.Add("Num. of Classes").Width= 100;
                    //add results to lsvAdmin     
                    for (int i = 0; i < dsPassType.Tables[0].Rows.Count; i++)
                    {
                        //create a list item to display in the list view "lsvAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsPassType.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsPassType.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        newItem.SubItems.Add(dsPassType.Tables[0].Rows[i].ItemArray.GetValue(2).ToString());
                        newItem.SubItems.Add(dsPassType.Tables[0].Rows[i].ItemArray.GetValue(3).ToString());
                        //add the new item to the list view (lsvAdmin)
                        lsvAdmin.Items.Add(newItem);
                    }
                    break;
                case "STAFF MEMBER":
                    //Hide not needed controls
                    lblPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Text = "";
                    //ensure required controls are visible and enabled
                    panel1.Enabled = true;
                    lsvAdmin.Enabled = true;
                    txtPurchAdminID.Enabled = true;
                    txtPurchAdminID.Visible = true;
                    txtPurchAdminID.Text = "";
                    txtPurchAdminName.Enabled = true;
                    txtPurchAdminName.Visible = true;
                    txtPurchAdminName.Text = "";                    
                    chkPurchAdminIsAdmin.Visible = true;
                    chkPurchAdminIsAdmin.Enabled = true;
                    lblPurchAdminFee.Visible = true;
                    lblPurchAdminFee.Text = "Password";
                    txtPurchAdminFee.Visible = true;
                    txtPurchAdminFee.Enabled = true;
                    txtPurchAdminFee.Text = "";
                    btnPurchAdminDelete.Enabled = true;
                    btnPurchAdminSelect.Enabled = true;
                    btnPurchAdminSubmit.Enabled = true;
                    //Lookup all Academys in the DB
                    string sqlSelectStaff = "SELECT * FROM StaffMember_Lookup;";
                    DataSet dsStaff = Utilities.SearchDatabase(sqlSelectStaff);
                    //Clear columns in list view
                    lsvAdmin.Clear();
                    lsvAdmin.Columns.Add("ID").Width = 50;
                    lsvAdmin.Columns.Add("Staff Name").Width = 200;
                    lsvAdmin.Columns.Add("Is Admin");
                    lsvAdmin.Columns.Add("Password");
                    //add results to lsvAdmin     
                    for (int i = 0; i < dsStaff.Tables[0].Rows.Count; i++)
                    {

                        //create a list item to display in the list view "lsvAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsStaff.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsStaff.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        newItem.SubItems.Add(dsStaff.Tables[0].Rows[i].ItemArray.GetValue(2).ToString());
                        newItem.SubItems.Add(dsStaff.Tables[0].Rows[i].ItemArray.GetValue(3).ToString());
                        //add the new item to the list view (lsvAdmin)
                        lsvAdmin.Items.Add(newItem);
                    }
                    break;
                case "PRODUCTS":
                    //Hide not needed controls
                    chkPurchAdminIsAdmin.Visible = false;                    
                    lblPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Visible = false;
                    txtPurchAdminNumClasses.Text = "";
                    //ensure required controls are visible and enabled
                    panel1.Enabled = true;
                    lsvAdmin.Enabled = true;
                    txtPurchAdminID.Enabled = true;
                    txtPurchAdminID.Visible = true;
                    txtPurchAdminID.Text = "";
                    txtPurchAdminName.Enabled = true;
                    txtPurchAdminName.Visible = true;
                    txtPurchAdminName.Text = "";
                    lblPurchAdminFee.Visible = true;
                    lblPurchAdminFee.Text = "Amount (Ex.GST)";
                    txtPurchAdminFee.Visible = true;
                    btnPurchAdminDelete.Enabled = true;
                    btnPurchAdminSelect.Enabled = true;
                    btnPurchAdminSubmit.Enabled = true;
                    //Lookup all Academys in the DB
                    string sqlProduct = "SELECT * FROM Product;";
                    DataSet dsProduct = Utilities.SearchDatabase(sqlProduct);
                    //Clear columns in list view
                    lsvAdmin.Clear();
                    lsvAdmin.Columns.Add("ID").Width = 50;
                    lsvAdmin.Columns.Add("Product Name").Width = 200;
                    lsvAdmin.Columns.Add("Price $").Width = 100;
                    //add results to lsvAdmin     
                    for (int i = 0; i < dsProduct.Tables[0].Rows.Count; i++)
                    {

                        //create a list item to display in the list view "lsvAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsProduct.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsProduct.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        newItem.SubItems.Add(dsProduct.Tables[0].Rows[i].ItemArray.GetValue(2).ToString());
                        //add the new item to the list view (lsvAdmin)
                        lsvAdmin.Items.Add(newItem);
                    }
                    break;
                default:
                    //reset the form
                    EnableControls(false);
                    setupPurchasingAdmin();
                    break;
            }
        }

        private void EnableControls(bool IsEnabled)
        {
            //Enable/Disable controls on the Purchasing Admin tab
            txtPurchAdminID.Enabled = IsEnabled;           
            txtPurchAdminFee.Enabled = IsEnabled;
            txtPurchAdminName.Enabled = IsEnabled;
            txtPurchAdminNumClasses.Enabled = IsEnabled;
            chkPurchAdminIsAdmin.Enabled = IsEnabled;
            btnPurchAdminSubmit.Enabled = IsEnabled;
            btnPurchAdminDelete.Enabled = IsEnabled;
            btnPurchAdminSelect.Enabled = IsEnabled;
            panel1.Enabled = IsEnabled;
        }

        private void setupPurchasingAdmin()
        {
            if (ddlToAdminister.SelectedIndex == 0)
            {
                chkPurchAdminIsAdmin.Visible = false;
                lblPurchAdminNumClasses.Visible = false;
                txtPurchAdminNumClasses.Visible = false;
            }
        }

        private void btnPurchAdminSelect_Click(object sender, EventArgs e)
        {
            //check an item in the lsvAdmin has been selected
            //test that an item has been selected
            if (lsvAdmin.SelectedIndices.Count == 0)
            {
                //message box that an item needs to be selcted.
                MessageBox.Show("Please select an item to Edit", "Edit an Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                //exit the function
                return;
            }

            //select the Update Selected radio button
            rdoPurchAdminUpdate.Checked = true;

            //remove the selected item from the list box
            int index;
            foreach (ListViewItem item in lsvAdmin.Items)
            {
                //only one item can be selected, as set by the properties of the listview box
                if (item.Selected)
                {
                    //set the index variable to use to remove the item from the _list
                    index = item.Index;
                    try
                    {
                        txtPurchAdminID.Enabled = false;
                        switch (ddlToAdminister.SelectedItem.ToString().ToUpper())
                        { //Items in ddl: Select ->, Affiliated Academy, Membership Type, Training Pass Type, Staff Member, Products

                            case "AFFILIATED ACADEMY":                                
                                txtPurchAdminID.Text = item.SubItems[0].Text;
                                txtPurchAdminName.Text = item.SubItems[1].Text;
                                break;
                            case "MEMBERSHIP TYPE":                            
                                txtPurchAdminID.Text = item.SubItems[0].Text;
                                txtPurchAdminName.Text = item.SubItems[1].Text;
                                txtPurchAdminFee.Text = item.SubItems[2].Text;
                                break;
                            case "TRAINING PASS TYPE":
                                txtPurchAdminID.Text = item.SubItems[0].Text;
                                txtPurchAdminName.Text = item.SubItems[1].Text;
                                txtPurchAdminFee.Text = item.SubItems[2].Text;
                                txtPurchAdminNumClasses.Text = item.SubItems[3].Text;
                                break;
                            case "STAFF MEMBER":
                                txtPurchAdminID.Text = item.SubItems[0].Text;
                                txtPurchAdminName.Text = item.SubItems[1].Text;
                                txtPurchAdminFee.Text = item.SubItems[3].Text;
                                chkPurchAdminIsAdmin.Checked = bool.Parse(item.SubItems[2].Text);
                                break;
                            case "PRODUCTS":
                                txtPurchAdminID.Text = item.SubItems[0].Text;
                                txtPurchAdminName.Text = item.SubItems[1].Text;
                                txtPurchAdminFee.Text = item.SubItems[2].Text;
                                break;

                            default:
                                break;
                        }                        

                    }
                    catch (Exception)
                    {
                        //message box that an error has occurred
                        MessageBox.Show("An error has occurred, please try again.", "Edit Item", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //exit the function
                        return;
                    }
                }
            }
        }

        private void btnPurchAdminDelete_Click(object sender, EventArgs e)
        {
            //check an item in the lsvAdmin has been selected
            //test that an item has been selected
            if (lsvAdmin.SelectedIndices.Count == 0)
            {
                //message box that an item needs to be selcted.
                MessageBox.Show("Please select an item to Delete", "Delete an Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                //exit the function
                return;
            }
            //remove the selected item from the list box
            int index;
            foreach (ListViewItem item in lsvAdmin.Items)
            {
                //only one item can be selected, as set by the properties of the listview box
                if (item.Selected)
                {
                    //set the index variable to use to remove the item from the _list
                    index = item.Index;
                    try
                    {
                        //prompt for confirmation of Delete
                        DialogResult result = MessageBox.Show(this, "Are you sure you want to PERMANENTLY delete: " + item.SubItems[1].Text + " from the Database?", "Delete an Item", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                        if (result == DialogResult.Yes)
                        {
                            //remove the select item from the listview box
                            lsvAdmin.Items.RemoveAt(index);
                            // Delete item from DB
                            string sqlDelete = "";
                            //confirm ID is a number
                            int intID = -1;
                            try
                            {
                                intID = int.Parse(item.SubItems[0].Text);
                            }
                            catch (Exception)
                            {
                                MessageBox.Show("ID field needs to be a number only, please check and try again.", "Error: ID field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                return; //exit the function
                            }
                            switch (ddlToAdminister.SelectedItem.ToString().ToUpper())
                            { //Items in ddl: Select ->, Affiliated Academy, Membership Type, Training Pass Type, Staff Member, Products

                                case "AFFILIATED ACADEMY":
                                    sqlDelete = "DELETE FROM Academy_Lookup WHERE ID='" + intID + "';";                                    
                                    break;
                                case "MEMBERSHIP TYPE":
                                    sqlDelete = "DELETE FROM MembershipType_Lookup WHERE ID='" + intID + "';";
                                    break;
                                case "TRAINING PASS TYPE":
                                    sqlDelete = "DELETE FROM PassType_Lookup WHERE ID='" + intID + "';";
                                    break;
                                case "STAFF MEMBER":
                                    sqlDelete = "DELETE FROM StaffMember_Lookup WHERE ID='" + intID + "';";
                                    break;
                                case "PRODUCTS":
                                    sqlDelete = "DELETE FROM Product WHERE ID='" + intID + "';";
                                    break;

                                default:
                                    break;
                            }
                            //run the delete statement
                            Utilities.DatabaseNonQuery(sqlDelete);

                            //show message item was deleted successfully
                            MessageBox.Show(item.SubItems[1].Text + " was deleted from the database successfully.", "Deletion successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                           
                            //only one item can be selected, so we don't need to test for any more selected items return out of this function - (developers decision, for simplicity)
                            return;
                        }

                    }
                    catch (Exception)
                    {
                        //message box that an error has occurred
                        MessageBox.Show("An error has occurred, please try again.", "Delete Item", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //exit the function
                        return;
                    }
                }
            }
        }

        private void btnPurchAdminSubmit_Click(object sender, EventArgs e)
        {
            //check that either Add or Insert radio buttons have been selected
            if (rdoPurchAdminInsert.Checked == false && rdoPurchAdminUpdate.Checked == false)
            {
                MessageBox.Show("Please select either 'Add New' or 'Update Selected'", "Warning: ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return; //exit the function
            }
            //Edit button changes the Button text to 'Update'
            //confirm ID is a number
            int intID = -1;
            try
            {
                intID = int.Parse(txtPurchAdminID.Text);
            }
            catch (Exception)
            {
                MessageBox.Show("ID field needs to be a number only, please check and try again.", "Error: ID field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return; //exit the function
            }
            if(rdoPurchAdminInsert.Checked){
                    #region Submit button text = ADD
                    //Insert to the apropriate table
                    string sqlInsert = "";
                    try
                    {
                        switch (ddlToAdminister.SelectedItem.ToString().ToUpper())
                        { //Items in ddl: Select ->, Affiliated Academy, Membership Type, Training Pass Type, Staff Member, Products

                            case "AFFILIATED ACADEMY":
                                sqlInsert = string.Format("INSERT INTO Academy_Lookup VALUES ({0},'{1}');", intID, txtPurchAdminName.Text);
                                break;
                            case "MEMBERSHIP TYPE":
                                double dblAmount = -1;
                                try
                                {
                                    dblAmount = double.Parse(txtPurchAdminFee.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Membership Amount field can only contain numbers, please check and try again.", "Error: Amount field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                }
                                sqlInsert = string.Format("INSERT INTO MembershipType_Lookup VALUES ({0},'{1}',{2});", intID, txtPurchAdminName.Text, dblAmount);
                                break;
                            case "TRAINING PASS TYPE":
                                dblAmount = -1;
                                try
                                {
                                    dblAmount = double.Parse(txtPurchAdminFee.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Membership Amount field can only contain numbers, please check and try again.", "Error: Amount field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                                int intNumClass = -1;
                                try
                                {
                                    intNumClass = int.Parse(txtPurchAdminNumClasses.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Number of Classes field can only contain numbers, please check and try again.", "Error: Number of Classes", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                                sqlInsert = string.Format("INSERT INTO PassType_Lookup VALUES ({0},'{1}',{2},'{3}');", intID, txtPurchAdminName.Text, dblAmount, intNumClass);
                                break;
                            case "STAFF MEMBER":
                                sqlInsert = string.Format("INSERT INTO StaffMember_Lookup VALUES ({0},'{1}',{2},'{3}');", intID, txtPurchAdminName.Text, chkPurchAdminIsAdmin.Checked, txtPurchAdminNumClasses.Text);
                                break;
                            case "PRODUCTS":
                                dblAmount = -1;
                                try
                                {
                                    dblAmount = double.Parse(txtPurchAdminFee.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Product Price field can only contain numbers, please check and try again.", "Error: Amount field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                                sqlInsert = string.Format("INSERT INTO Product VALUES ({0},'{1}',{2});", intID, txtPurchAdminName.Text, dblAmount);
                                break;

                            default:
                                break;
                        }
                        //Insert into DB
                        Utilities.DatabaseNonQuery(sqlInsert);

                        //Show success message
                        MessageBox.Show("Item was successfully inserted into the database.\r\n\r\nRe-select 'Select to Administer' to see new item.", "Insert successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (Exception ex)
                    {
                        //Something went wrong with the INSERT
                        MessageBox.Show("Insert into the database failed:\r\n\r\n" + ex.Message.ToString(), "Insert failed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
#endregion
            }
            else if(rdoPurchAdminUpdate.Checked)
            {
                //check an item in the lsvAdmin has been selected
                //test that an item has been selected
                if (lsvAdmin.SelectedIndices.Count == 0)
                {
                    //message box that an item needs to be selcted.
                    MessageBox.Show("Please select an item to Edit", "Edit an Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    //exit the function
                    return;
                }
                    #region Submit button text = UPDATE
                    //Update the appropriate table
                    //Insert to the apropriate table
                    string sqlUpdate = "";
                    try
                    {
                        switch (ddlToAdminister.SelectedItem.ToString().ToUpper())
                        { //Items in ddl: Select ->, Affiliated Academy, Membership Type, Training Pass Type, Staff Member, Products

                            case "AFFILIATED ACADEMY":
                                sqlUpdate = string.Format("UPDATE Academy_Lookup SET ID={0},TYPE='{1}' WHERE ID={0};", intID, txtPurchAdminName.Text);
                                break;
                            case "MEMBERSHIP TYPE":
                                double dblAmount = -1;
                                try
                                {
                                    dblAmount = double.Parse(txtPurchAdminFee.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Membership Amount field can only contain numbers, please check and try again.", "Error: Amount field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                }
                                sqlUpdate = string.Format("UPDATE MembershipType_Lookup SET ID={0},TYPE='{1}',FEE={2} WHERE ID={0};", intID, txtPurchAdminName.Text, dblAmount);
                                break;
                            case "TRAINING PASS TYPE":
                                dblAmount = -1;
                                try
                                {
                                    dblAmount = double.Parse(txtPurchAdminFee.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Membership Amount field can only contain numbers, please check and try again.", "Error: Amount field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                                int intNumClass = -1;
                                try
                                {
                                    intNumClass = int.Parse(txtPurchAdminNumClasses.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Number of Classes field can only contain numbers, please check and try again.", "Error: Number of Classes", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                                sqlUpdate = string.Format("UPDATE PassType_Lookup SET ID={0},TYPE='{1}',FEE={2},NumberOfClasses='{3}' WHERE ID={0};", intID, txtPurchAdminName.Text, dblAmount, intNumClass);
                                break;
                            case "STAFF MEMBER":
                                sqlUpdate = string.Format("UPDATE StaffMember_Lookup SET ID={0},STAFFNAME='{1}',IsAdmin={2},Password='{3}' WHERE ID={0};", intID, txtPurchAdminName.Text, chkPurchAdminIsAdmin.Checked, txtPurchAdminNumClasses.Text);
                                break;
                            case "PRODUCTS":
                                dblAmount = -1;
                                try
                                {
                                    dblAmount = double.Parse(txtPurchAdminFee.Text);
                                }
                                catch (Exception)
                                {
                                    MessageBox.Show("Product Price field can only contain numbers, please check and try again.", "Error: Amount field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                                sqlUpdate = string.Format("UPDATE Product SET ID={0},NAME='{1}',PriceExGST={2} WHERE ID={0};", intID, txtPurchAdminName.Text, dblAmount);
                                break;

                            default:
                                break;
                        }
                        //Insert into DB
                        Utilities.DatabaseNonQuery(sqlUpdate);

                        //Show success message
                        MessageBox.Show("Item was successfully updated in the database.\r\n\r\nRe-select 'Select to Administer' to see the edited item.", "Update successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (Exception ex)
                    {
                        //Something went wrong with the INSERT
                        MessageBox.Show("Update into the database failed:\r\n\r\n" + ex.Message.ToString(), "Update failed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
                                    
                    #endregion
            }
        }

        private void rdoPurchAdminInsert_CheckedChanged(object sender, EventArgs e)
        {
            //Clear the fields
            txtPurchAdminFee.Text = "";
            txtPurchAdminID.Text = "";
            txtPurchAdminName.Text = "";
            txtPurchAdminNumClasses.Text = "";
            chkPurchAdminIsAdmin.Checked = false;
            //enable the ID field
            txtPurchAdminID.Enabled = true;

        }

        private void rdoPurchAdminUpdate_CheckedChanged(object sender, EventArgs e)
        {
            //disable the ID field
            txtPurchAdminID.Enabled = false;
        }

        private void tabControl3_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void tabAssignGrade_Click(object sender, EventArgs e)
        {

        }




        private void cmbClassAdminSelect_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Items in ddl: Select ->, Class Style, Class Type, Rank

            //items should be disabled
            EnableClassAdminControls(false);
            setupClassAdmin();

            //on selection - enable required controls, change lables, lookup existing items in db, and fill the list view
            switch (cmbClassAdminSelect.SelectedItem.ToString().ToUpper())
            {
                case "FIGHTING STYLE": 
                    //ensure required controls are visible and enabled
                    lsvClassAdmin.Enabled = true;
                    txtClassAdminID.Enabled = true;
                    txtClassAdminName.Enabled = true;
                    btnClassAdminEdit.Enabled = true;
                    btnClassAdminDelete.Enabled = true;
                    btnClassAdminSubmit.Enabled = true;
                    pnlClassAdminPanel.Enabled = true;
                    rdoClassAdminInsert.Enabled = true;
                    rdoClassAdminUpdate.Enabled = true;

                    //Lookup all Fighting Styles in the DB
                    string sqlSelectFightingStyle = "SELECT * FROM FightingStyle_Lookup;";
                    DataSet dsFightingStyle = Utilities.SearchDatabase(sqlSelectFightingStyle);
                    //Clear columns in list view
                    lsvClassAdmin.Clear();
                    lsvClassAdmin.Columns.Add("ID").Width = 50;
                    lsvClassAdmin.Columns.Add("Fighting Style").Width = 300;
                    //add results to lsvAdmin     
                    for (int i = 0; i < dsFightingStyle.Tables[0].Rows.Count; i++)
                    {
                        //create a list item to display in the list view "lsvClassAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsFightingStyle.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsFightingStyle.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        //add the new item to the list view (lsvClassAdmin)
                        lsvClassAdmin.Items.Add(newItem);
                    }


                    break;
                case "CLASS TYPE":
                    //ensure required controls are visible and enabled
                    lsvClassAdmin.Enabled = true;
                    txtClassAdminID.Enabled = true;
                    txtClassAdminName.Enabled = true;
                    btnClassAdminEdit.Enabled = true;
                    btnClassAdminDelete.Enabled = true;
                    btnClassAdminSubmit.Enabled = true;
                    pnlClassAdminPanel.Enabled = true;
                    rdoClassAdminInsert.Enabled = true;
                    rdoClassAdminUpdate.Enabled = true;

                    //Lookup all Class Types in the DB
                    string sqlSelectClassType = "SELECT * FROM ClassType_Lookup;";
                    DataSet dsClassType = Utilities.SearchDatabase(sqlSelectClassType);
                    //Clear columns in list view
                    lsvClassAdmin.Clear();
                    lsvClassAdmin.Columns.Add("ID").Width = 50;
                    lsvClassAdmin.Columns.Add("Class Type").Width = 200;
                    //add results to lsvClassAdmin     
                    for (int i = 0; i < dsClassType.Tables[0].Rows.Count; i++)
                    {
                        //create a list item to display in the list view "lsvClassAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsClassType.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsClassType.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        //add the new item to the list view (lsvClassAdmin)
                        lsvClassAdmin.Items.Add(newItem);
                    }
                    break;
                case "RANK":
                    //ensure required controls are visible and enabled
                    lsvClassAdmin.Enabled = true;
                    txtClassAdminID.Enabled = true;
                    txtClassAdminName.Enabled = true;
                    btnClassAdminEdit.Enabled = true;
                    btnClassAdminDelete.Enabled = true;
                    btnClassAdminSubmit.Enabled = true;
                    pnlClassAdminPanel.Enabled = true;
                    rdoClassAdminInsert.Enabled = true;
                    rdoClassAdminUpdate.Enabled = true;

                    //Lookup all Ranks in the DB
                    string sqlSelectRank = "SELECT * FROM JJJRank_Lookup;";
                    DataSet dsRank = Utilities.SearchDatabase(sqlSelectRank);
                    //Clear columns in list view
                    lsvClassAdmin.Clear();
                    lsvClassAdmin.Columns.Add("ID").Width = 50;
                    lsvClassAdmin.Columns.Add("Rank").Width = 200;
                    //add results to lsvClassAdmin     
                    for (int i = 0; i < dsRank.Tables[0].Rows.Count; i++)
                    {
                        //create a list item to display in the list view "lsvClassAdmin" and
                        //add items to each column
                        ListViewItem newItem = new ListViewItem(dsRank.Tables[0].Rows[i].ItemArray.GetValue(0).ToString());
                        newItem.SubItems.Add(dsRank.Tables[0].Rows[i].ItemArray.GetValue(1).ToString());
                        //add the new item to the list view (lsvClassAdmin)
                        lsvClassAdmin.Items.Add(newItem);
                    }
                    break;
                default:
                    //reset the form
                    EnableClassAdminControls(false);
                    setupPurchasingAdmin();
                    break;
            }

        }

        private void btnClassAdminEdit_Click(object sender, EventArgs e)
        {
            //check an item in the lsvAdmin has been selected
            //test that an item has been selected
            if (lsvClassAdmin.SelectedIndices.Count == 0)
            {
                //message box that an item needs to be selcted.
                MessageBox.Show("Please select an item to Edit", "Edit an Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                //exit the function
                return;
            }

            //select the Update Selected radio button
            rdoClassAdminUpdate.Checked = true;

            //remove the selected item from the list box
            int index;
            foreach (ListViewItem item in lsvClassAdmin.Items)
            {
                //only one item can be selected, as set by the properties of the listview box
                if (item.Selected)
                {
                    //set the index variable to use to remove the item from the _list
                    index = item.Index;
                    try
                    {
                        txtClassAdminID.Enabled = false;
                        switch (cmbClassAdminSelect.SelectedItem.ToString().ToUpper())
                        { //Items in ddl: Select ->, FIGHTING STYLE, CLASS TYPE, RANK

                            case "FIGHTING STYLE":
                            case "CLASS TYPE":
                            case "RANK":
                                txtClassAdminID.Text = item.SubItems[0].Text;
                                txtClassAdminName.Text = item.SubItems[1].Text;
                                break;

                            default:
                                break;
                        }

                    }
                    catch (Exception)
                    {
                        //message box that an error has occurred
                        MessageBox.Show("An error has occurred, please try again.", "Edit Item", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //exit the function
                        return;
                    }
                }
            }

        }

        private void btnClassAdminDelete_Click(object sender, EventArgs e)
        {
            //check an item in the lsvAdmin has been selected
            //test that an item has been selected
            if (lsvClassAdmin.SelectedIndices.Count == 0)
            {
                //message box that an item needs to be selcted.
                MessageBox.Show("Please select an item to Delete", "Delete an Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                //exit the function
                return;
            }
            //remove the selected item from the list box
            int index;
            foreach (ListViewItem item in lsvClassAdmin.Items)
            {
                //only one item can be selected, as set by the properties of the listview box
                if (item.Selected)
                {
                    //set the index variable to use to remove the item from the _list
                    index = item.Index;
                    try
                    {
                        //prompt for confirmation of Delete
                        DialogResult result = MessageBox.Show(this, "Are you sure you want to PERMANENTLY delete: " + item.SubItems[1].Text + " from the Database?", "Delete an Item", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                        if (result == DialogResult.Yes)
                        {
                            //remove the select item from the listview box
                            lsvClassAdmin.Items.RemoveAt(index);
                            // Delete item from DB
                            string sqlDelete = "";
                            //confirm ID is a number
                            int intID = -1;
                            try
                            {
                                intID = int.Parse(item.SubItems[0].Text);
                            }
                            catch (Exception)
                            {
                                MessageBox.Show("ID field needs to be a number only, please check and try again.", "Error: ID field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                return; //exit the function
                            }
                            switch (cmbClassAdminSelect.SelectedItem.ToString().ToUpper())
                            { //Items in ddl: Select ->, Affiliated Academy, Membership Type, Training Pass Type, Staff Member, Products

                                case "FIGHTING STYLE":
                                    sqlDelete = "DELETE FROM FightingStyle_Lookup WHERE ID='" + intID + "';";
                                    break;
                                case "CLASS TYPE":
                                    sqlDelete = "DELETE FROM ClassType_Lookup WHERE ID='" + intID + "';";
                                    break;
                                case "RANK":
                                    sqlDelete = "DELETE FROM JJJRank_Lookup WHERE ID='" + intID + "';";
                                    break;

                                default:
                                    break;
                            }
                            //run the delete statement
                            Utilities.DatabaseNonQuery(sqlDelete);

                            //show message item was deleted successfully
                            MessageBox.Show(item.SubItems[1].Text + " was deleted from the database successfully.", "Deletion successful", MessageBoxButtons.OK, MessageBoxIcon.Information);

                            //only one item can be selected, so we don't need to test for any more selected items return out of this function - (developers decision, for simplicity)
                            return;
                        }

                    }
                    catch (Exception)
                    {
                        //message box that an error has occurred
                        MessageBox.Show("An error has occurred, please try again.", "Delete Item", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //exit the function
                        return;
                    }
                }
            }
        }

        private void btnClassAdminSubmit_Click(object sender, EventArgs e)
        {
            //check that either Add or Insert radio buttons have been selected
            if (rdoClassAdminInsert.Checked == false && rdoClassAdminUpdate.Checked == false)
            {
                MessageBox.Show("Please select either 'Add New' or 'Update Selected'", "Warning: ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return; //exit the function
            }
            //Edit button changes the Button text to 'Update'
            //confirm ID is a number
            int intID = -1;
            try
            {
                intID = int.Parse(txtClassAdminID.Text);
            }
            catch (Exception)
            {
                MessageBox.Show("ID field needs to be a number only, please check and try again.", "Error: ID field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return; //exit the function
            }
            if (rdoClassAdminInsert.Checked)
            {
                #region Submit button text = ADD
                //Insert to the apropriate table
                string sqlInsert = "";
                try
                {
                    switch (cmbClassAdminSelect.SelectedItem.ToString().ToUpper())
                    { //Items in ddl: Select ->, Fighting Style, Class type, rank

                        case "FIGHTING STYLE":
                            sqlInsert = string.Format("INSERT INTO FightingStyle_Lookup VALUES ({0},'{1}');", intID, txtClassAdminName.Text);
                            break;
                        case "CLASS TYPE":
                            sqlInsert = string.Format("INSERT INTO ClassType_Lookup VALUES ({0},'{1}');", intID, txtClassAdminName.Text);
                            break;
                        case "RANK":
                            sqlInsert = string.Format("INSERT INTO JJJRank_Lookup VALUES ({0},'{1}');", intID, txtClassAdminName.Text);
                            break;
                        default:
                            break;
                    }
                    //Insert into DB
                    Utilities.DatabaseNonQuery(sqlInsert);

                    //Show success message
                    MessageBox.Show("Item was successfully inserted into the database.\r\n\r\nRe-select 'Select to Administer' to see new item.", "Insert successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    //Something went wrong with the INSERT
                    MessageBox.Show("Insert into the database failed:\r\n\r\n" + ex.Message.ToString(), "Insert failed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                #endregion
            }
            else if (rdoClassAdminUpdate.Checked)
            {
                //check an item in the lsvClassAdmin has been selected
                //test that an item has been selected
                if (lsvClassAdmin.SelectedIndices.Count == 0)
                {
                    //message box that an item needs to be selcted.
                    MessageBox.Show("Please select an item to Edit", "Edit an Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    //exit the function
                    return;
                }
                #region Submit button text = UPDATE
                //Update the appropriate table
                //Insert to the apropriate table
                string sqlUpdate = "";
                try
                {
                    switch (cmbClassAdminSelect.SelectedItem.ToString().ToUpper())
                    { //Items in ddl: Select ->, Fighting Style, Class type, rank

                        case "FIGHTING STYLE":
                            sqlUpdate = string.Format("UPDATE FightingStyle_Lookup SET ID={0},TYPE='{1}' WHERE ID={0};", intID, txtClassAdminName.Text);
                            break;
                        case "CLASS TYPE":
                            sqlUpdate = string.Format("UPDATE ClassType_Lookup SET ID={0},TYPE='{1}' WHERE ID={0};", intID, txtClassAdminName.Text);
                            break;
                        case "RANK":
                            sqlUpdate = string.Format("UPDATE JJJRank_Lookup SET ID={0},TYPE='{1}' WHERE ID={0};", intID, txtClassAdminName.Text);
                            break;
                        default:
                            break;
                    }
                    //Insert into DB
                    Utilities.DatabaseNonQuery(sqlUpdate);

                    //Show success message
                    MessageBox.Show("Item was successfully updated in the database.\r\n\r\nRe-select 'Select to Administer' to see the edited item.", "Update successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    //Something went wrong with the INSERT
                    MessageBox.Show("Update into the database failed:\r\n\r\n" + ex.Message.ToString(), "Update failed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                #endregion
            }
        }

        private void EnableClassAdminControls(bool IsEnabled)
        {
            //Enable/Disable controls on the Class Admin tab
            txtClassAdminID.Enabled = IsEnabled;
            txtClassAdminName.Enabled = IsEnabled;
            btnClassAdminSubmit.Enabled = IsEnabled;
            btnClassAdminDelete.Enabled = IsEnabled;
            btnClassAdminEdit.Enabled = IsEnabled;
            pnlClassAdminPanel.Enabled = IsEnabled;

        }

        private void setupClassAdmin()
        {
            if (ddlToAdminister.SelectedIndex == 0)
            {

            }
        }
    }
}
